using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;

using GonzalesCore.CoreElements;
using Kypris2.CoreElements;
using Kypris2.CoreManagers;
using Jamila2.CoreElements;
using Jamila2.Tools;
using Jamila2.Database;

namespace GonzalesCore.CoreManagers
{
    public class CreditCardManager : KyprisManagerBase, IManager, IDisposable
    {
        private GonzalesDataStructure DataStructrure = new GonzalesDataStructure();
        private PersonalSessionAccount CurrentModuleSession = null;
        private LogManager CurrentLogManager = null;

        public CreditCardManager(PersonalSessionAccount PrivateConfig)
            : base(PrivateConfig)
        {
            this.CurrentModuleSession = PrivateConfig;
            this.CurrentLogManager = new LogManager(PrivateConfig);
        }

        #region Enum

        #region Credit Card Payment Type
        public static int GetCreditCardPaymentTypeInCode(TypeOfCreditCardPayment CreditCardPaymentType)
        {
            int result = 0;

            switch (CreditCardPaymentType)
            {
                case TypeOfCreditCardPayment.PaidByCompany:
                    result = 0;
                    break;

                case TypeOfCreditCardPayment.PaidByEmployee:
                    result = 1;
                    break;
            }

            return result;
        }

        public static TypeOfCreditCardPayment GetCreditCardPaymentTypeFromCode(int CreditCardPaymentType)
        {
            TypeOfCreditCardPayment result = TypeOfCreditCardPayment.PaidByCompany;

            switch (CreditCardPaymentType)
            {
                case 0:
                    result = TypeOfCreditCardPayment.PaidByCompany;
                    break;

                case 1:
                    result = TypeOfCreditCardPayment.PaidByEmployee;
                    break;
            }

            return result;
        }

        public static string GetCreditCardPaymentTypeInString(TypeOfCreditCardPayment CreditCardPaymentType)
        {
            string result = "Paid By Company";

            switch (CreditCardPaymentType)
            {
                case TypeOfCreditCardPayment.PaidByCompany:
                    result = "Paid By Company";
                    break;

                case TypeOfCreditCardPayment.PaidByEmployee:
                    result = "Paid By Employee";
                    break;
            }

            return result;
        }

        #endregion

        #endregion

        #region Display

        #region GetActualItemsByEmployee
        public CreditCardItems GetActualItemsByEmployee(string EmployeeID)
        {
            CreditCardItems ResultItems = null;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.EmployeeNo.ActualFieldName, EmployeeID));

            DataTable ResultTable = null;
            if ((ResultTable = this.QueryActualCreditCard(KeyParameters)) != null)
            {
                ResultItems = this.LoadCreditCardItemsInActual(ResultTable);
            }
            else
            {
                this.CurrentLogManager.LogError("[CreditCardManager.GetActualItemsByEmployee] : DataTable is Null : " + this.ErrorMessage, this.CurrentModuleSession.CurrentLoggedOnUser, LogInterface.User);
            }

            return ResultItems;
        }

        public CreditCardItems GetActualItemsByEmployee(string EmployeeID, bool IsSelected)
        {
            CreditCardItems ResultItems = null;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.EmployeeNo.ActualFieldName, EmployeeID));
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.IsSelected.ActualFieldName, ((IsSelected) ? "1" : "0")));

            DataTable ResultTable = null;
            if ((ResultTable = this.QueryActualCreditCard(KeyParameters)) != null)
            {
                ResultItems = this.LoadCreditCardItemsInActual(ResultTable);
            }
            else
            {
                this.CurrentLogManager.LogError("[CreditCardManager.GetActualItemsByEmployee] : DataTable is Null : " + this.ErrorMessage, this.CurrentModuleSession.CurrentLoggedOnUser, LogInterface.System);
            }

            return ResultItems;
        }
        #endregion        

        #region GetAvailableItemsByEmployee

        public CreditCardItems GetAvailableItemsByEmployee(string EmployeeID)
        {
            CreditCardItems ResultItems = null;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.StoredProcedures.GetAvailableCreditCardItems.Param_EmployeeID.ActualFieldName, EmployeeID));

            if (this.TryConnection())
            {
                this.CurSQLFactory.ExecuteStoredProcedure(this.DataStructrure.StoredProcedures.GetAvailableCreditCardItems.ActualTableName, KeyParameters);
                DataTable ResultTable = this.CurDBEngine.SelectQuery(this.CurSQLFactory.SQL);

                if (ResultTable != null)
                {
                    ResultItems = this.LoadCreditCardItemsInActual(ResultTable);

                    // load again with extra properties
                    for (int x = 0; x < ResultTable.Rows.Count; x++)
                    {
                        if (ResultItems[x].AttachedToClaimHeaderID.Length == 36)
                        {
                            // claim header
                            ResultItems[x].AttahcedToClaimHeader = new GonzalesClaimHeader(
                                ResultItems[x].AttachedToClaimHeaderID,
                                ResultTable.Rows[x][this.DataStructrure.StoredProcedures.GetAvailableCreditCardItems.ClaimReasons.ActualFieldName].ToString());
                            ResultItems[x].AttahcedToClaimHeader.CreatedOn = DateTime.Parse(ResultTable.Rows[x][this.DataStructrure.StoredProcedures.GetAvailableCreditCardItems.ClaimCreated.ActualFieldName].ToString());

                            // claim detail
                            ResultItems[x].AttahcedToClaimDetail = new GonzalesClaimItem(
                                ResultItems[x].AttachedToClaimDetailID);
                        }
                    }
                }
                else
                {
                    this.ErrMsg = "[CreditCardManager.GetAvailableItemsByEmployee] : ResultTable is null : " + this.CurDBEngine.ErrorMessage;
                }
            }

            return ResultItems;
        }

        #endregion

        #region GetAvailableCreditCardItemByCompany
        public DataTable GetAvailableCreditCardItemByCompany(string CompanyID, bool IsSelected)
        {
            DataTable ResultItems = null;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Views.OpCCActual.CompanyInternalID.ActualFieldName, CompanyID));
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Views.OpCCActual.Selected.ActualFieldName, ((IsSelected) ? "1" : "0")));

            DataTable ResultTable = null;
            if ((ResultTable = this.QueryActualCreditCardView(KeyParameters)) != null)
            {
                ResultItems = ResultTable;
            }
            else
            {
                this.CurrentLogManager.LogError("[CreditCardManager.GetAvailableCreditCardItemByCompany] : DataTable is Null : " + this.ErrorMessage, this.CurrentModuleSession.CurrentLoggedOnUser, LogInterface.System);
            }

            return ResultItems;
        }
        #endregion

        #endregion

        #region Action

        #region UpdateActualItems
        public bool UpdateActualItems(CreditCardItems ItemsCollection, string ClaimHeaderID)
        {
            bool smooth = true;
            ArrayList sql = new ArrayList();
            DatabaseParameters KeyParameters = null, ValParameters = null;

            for (int x = 0; x < ItemsCollection.Count; x++)
            {
                // get those selected Credit Card Item, with din't attached to any claim
                if (ItemsCollection[x].IsSelected && ItemsCollection[x].AttachedToClaimHeaderID.Length == 0)
                {
                    #region Update Credit Card Item

                    // assign a unique id to claim detail
                    // become the id for claim detail forever
                    ItemsCollection[x].AttachedToClaimDetailID = SwissArmy.UniqueID();

                    // update credit card item first
                    KeyParameters = new DatabaseParameters();
                    KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.InternalID.ActualFieldName, ItemsCollection[x].InternalID));

                    ValParameters = new DatabaseParameters();
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.ClaimHeaderID.ActualFieldName, ClaimHeaderID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.ClaimItemID.ActualFieldName, ItemsCollection[x].AttachedToClaimDetailID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.IsSelected.ActualFieldName, "1"));

                    this.CurSQLFactory.UpdateCommand(KeyParameters, ValParameters, this.DataStructrure.Tables.CreditCardActual.ActualTableName);
                    sql.Add(this.CurSQLFactory.SQL);

                    #endregion

                    #region Insert New Claim Detail

                    // insert into claim detail
                    ValParameters = new DatabaseParameters();
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.InternalID.ActualFieldName, ItemsCollection[x].AttachedToClaimDetailID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ClaimID.ActualFieldName, ClaimHeaderID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.DetailType.ActualFieldName, "1"));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ClaimDate.ActualFieldName, ItemsCollection[x].TransactionDate));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.Description.ActualFieldName, ItemsCollection[x].Description.Replace("'", "''"), true));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountClaimed.ActualFieldName, ItemsCollection[x].SpentAmount.ToString()));
                    if (ItemsCollection[x].SpentCurrency != null && ItemsCollection[x].SpentCurrency.InternalID.Length > 0)
                    {
                        ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountClaimedCurrency.ActualFieldName, ItemsCollection[x].SpentCurrency.InternalID));
                        ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ExchangeRate.ActualFieldName, ItemsCollection[x].SpentCurrency.InternalID));
                    }
                    else
                    {
                        ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountClaimedCurrency.ActualFieldName, "OTH"));
                        ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ExchangeRate.ActualFieldName, "OTH"));
                    }
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ExchangeRateFigure.ActualFieldName, ItemsCollection[x].ExchangeRate.ToString()));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountOut.ActualFieldName, ItemsCollection[x].FinalAmount.ToString()));

                    if (ItemsCollection[x].FinalCurrency != null && ItemsCollection[x].FinalCurrency.InternalID.Length > 0)
                        ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountOutCurrency.ActualFieldName, ItemsCollection[x].FinalCurrency.InternalID));
                    else
                        ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountOutCurrency.ActualFieldName, "OTH"));

                    this.CurSQLFactory.InsertCommand(ValParameters, this.DataStructrure.Tables.ClaimDetail.ActualTableName);
                    sql.Add(this.CurSQLFactory.SQL);

                    #endregion

                }

                // get those selected Credit Card Item, with attached to SOME OTHER claim
                if (ItemsCollection[x].IsSelected && ItemsCollection[x].AttachedToClaimHeaderID.Length == 36 && ItemsCollection[x].AttachedToClaimHeaderID.CompareTo(ClaimHeaderID) != 0) 
                {
                    #region Update Credit Card Item

                    // assign a unique id to claim detail
                    // become the id for claim detail forever
                    ItemsCollection[x].AttachedToClaimDetailID = SwissArmy.UniqueID();

                    // update credit card item first
                    KeyParameters = new DatabaseParameters();
                    KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.InternalID.ActualFieldName, ItemsCollection[x].InternalID));

                    ValParameters = new DatabaseParameters();
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.ClaimHeaderID.ActualFieldName, ClaimHeaderID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.ClaimItemID.ActualFieldName, ItemsCollection[x].AttachedToClaimDetailID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.IsSelected.ActualFieldName, "1"));

                    this.CurSQLFactory.UpdateCommand(KeyParameters, ValParameters, this.DataStructrure.Tables.CreditCardActual.ActualTableName);
                    sql.Add(this.CurSQLFactory.SQL);

                    #endregion

                    #region Insert New Claim Detail

                    // insert into claim detail
                    ValParameters = new DatabaseParameters();
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.InternalID.ActualFieldName, ItemsCollection[x].AttachedToClaimDetailID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ClaimID.ActualFieldName, ClaimHeaderID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.DetailType.ActualFieldName, "1"));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ClaimDate.ActualFieldName, ItemsCollection[x].TransactionDate));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.Description.ActualFieldName, ItemsCollection[x].Description.Replace("'", "''"), true));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountClaimed.ActualFieldName, ItemsCollection[x].SpentAmount.ToString()));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountClaimedCurrency.ActualFieldName, ItemsCollection[x].SpentCurrency.InternalID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ExchangeRate.ActualFieldName, ItemsCollection[x].SpentCurrency.InternalID));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.ExchangeRateFigure.ActualFieldName, ItemsCollection[x].ExchangeRate.ToString()));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountOut.ActualFieldName, ItemsCollection[x].FinalAmount.ToString()));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.AmountOutCurrency.ActualFieldName, ItemsCollection[x].FinalCurrency.InternalID));

                    this.CurSQLFactory.InsertCommand(ValParameters, this.DataStructrure.Tables.ClaimDetail.ActualTableName);
                    sql.Add(this.CurSQLFactory.SQL);

                    #endregion

                }

                // get those un-selected Credit Card Item, but with attached to some claims, need to remove
                if (!ItemsCollection[x].IsSelected && ItemsCollection[x].AttachedToClaimHeaderID.Length == 36)
                {
                    #region Update Credit Card Item

                    // update credit card item first
                    KeyParameters = new DatabaseParameters();
                    KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.InternalID.ActualFieldName, ItemsCollection[x].InternalID));

                    ValParameters = new DatabaseParameters();
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.ClaimHeaderID.ActualFieldName, "", true));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.ClaimItemID.ActualFieldName, "", true));
                    ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.IsSelected.ActualFieldName, "0"));

                    this.CurSQLFactory.UpdateCommand(KeyParameters, ValParameters, this.DataStructrure.Tables.CreditCardActual.ActualTableName);
                    sql.Add(this.CurSQLFactory.SQL);

                    #endregion

                    #region Delete From Claim Detail

                    KeyParameters = new DatabaseParameters();
                    KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.InternalID.ActualFieldName, ItemsCollection[x].AttachedToClaimDetailID));
                    KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.ClaimDetail.Parents.ActualFieldName, ItemsCollection[x].AttachedToClaimDetailID, DBDataType.String, DBLinkage.OR));

                    this.CurSQLFactory.DeleteCommand(KeyParameters, this.DataStructrure.Tables.ClaimDetail.ActualTableName);
                    sql.Add(this.CurSQLFactory.SQL);

                    #endregion
                }
            }

            // update claim header in total
            KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.StoredProcedures.UpdateClaimHeaderAmount.Param_ClaimHeaderID.ActualFieldName, ClaimHeaderID));
            this.CurSQLFactory.ExecuteStoredProcedure(this.DataStructrure.StoredProcedures.UpdateClaimHeaderAmount.ActualTableName, KeyParameters);
            sql.Add(this.CurSQLFactory.SQL);

            // Exisiting credit card item in Claim Header, ignore them
            // as they will never change

            if (this.TryConnection())
            {
                smooth = this.CurDBEngine.ExecuteQuery(sql);
                if (!smooth)
                {
                    this.ErrMsg = "[CreditCardManager.UpdateActualItems] : SQL Execution Error : " + this.CurDBEngine.ErrorMessage;
                }
            }

            return smooth;
        }


        #endregion        
        
        #region ClearCreditCardItems
        public bool ClearCreditCardItems(string ClaimantID)
        {
            bool smooth = true;

            DatabaseParameters KeyParameters = new DatabaseParameters();
            KeyParameters.Add(new DatabaseParameter(this.DataStructrure.StoredProcedures.ClearCreditCardItems.Param_EmployeeID.ActualFieldName, ClaimantID));

            if (this.TryConnection())
            {
                this.CurSQLFactory.ExecuteStoredProcedure(this.DataStructrure.StoredProcedures.ClearCreditCardItems.ActualTableName, KeyParameters);
                smooth = this.CurDBEngine.ExecuteQuery(this.CurSQLFactory.SQL);

                if (!smooth)
                {
                    this.ErrMsg = this.CurDBEngine.ErrorMessage;
                }
            }

            return smooth;
        }
        #endregion

        #region DuplicateLineItem
        public bool DuplicateLineItem(string SourceID, decimal NewAmount)
        {
            bool smooth = true;

            if (this.TryConnection())
            {
                DatabaseParameters KeyParameters = new DatabaseParameters();
                                
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.StoredProcedures.DuplicateCreditCardLineItem.Param_SourceID.ActualFieldName, SourceID));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.StoredProcedures.DuplicateCreditCardLineItem.Param_NewAmount.ActualFieldName, NewAmount.ToString()));

                this.CurSQLFactory.ExecuteStoredProcedure(this.DataStructrure.StoredProcedures.DuplicateCreditCardLineItem.ActualTableName, KeyParameters);

                if (!(smooth = this.CurDBEngine.ExecuteQuery(this.CurSQLFactory.SQL)))
                {
                    this.ErrMsg = "[CreditCardManager.MergeLineItem] : this.CurDBEngine.ExecuteQuery('" + this.CurSQLFactory.SQL + "') : " + this.CurDBEngine.ErrorMessage;
                }
            }
            else
            {
                this.ErrMsg = "[CreditCardManager.MergeLineItem] : Exception : " + this.ErrMsg;
            }


            return smooth;

        }
        #endregion

        #region MergeLineItem

        public bool MergeLineItem(string SourceID, string[] MergedID, decimal NewAmount)
        {
            bool smooth = false;
            ArrayList sql = new ArrayList();

            if (this.TryConnection())
            {
                DatabaseParameters KeyParameters = new DatabaseParameters();
                DatabaseParameters ValParameters = new DatabaseParameters();

                // update the main
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.InternalID.ActualFieldName, SourceID));
                ValParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.AmountFinal.ActualFieldName, NewAmount.ToString()));

                this.CurSQLFactory.UpdateCommand(KeyParameters, ValParameters, this.DataStructrure.Tables.CreditCardActual.ActualTableName);
                sql.Add(this.CurSQLFactory.SQL);

                // delete the rest
                for (int x = 0; x < MergedID.Length; x++)
                {
                    KeyParameters.Clear();
                    KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.CreditCardActual.InternalID.ActualFieldName, MergedID[x]));

                    this.CurSQLFactory.DeleteCommand(KeyParameters, this.DataStructrure.Tables.CreditCardActual.ActualTableName);
                    sql.Add(this.CurSQLFactory.SQL);
                }

                if (!(smooth = this.CurDBEngine.ExecuteQuery(sql)))
                {
                    this.ErrMsg = "[CreditCardManager.MergeLineItem] : this.CurDBEngine.ExecuteQuery() : " + this.CurDBEngine.ErrorMessage;
                }


            }
            else
            {
                this.ErrMsg = "[CreditCardManager.MergeLineItem] : Exception : " + this.ErrMsg;
            }

            return smooth;

        }

        #endregion

        #region InjectCreditCardItems
        public bool InjectCreditCardItems(CreditCardItems ItemsCollection)
        {
            bool smooth = true;
            ArrayList sql = new ArrayList();
            DatabaseParameters KeyParameters = new DatabaseParameters();

            // clear tmp table
            //this.CurSQLFactory.DeleteCommand((new DatabaseParameters()), this.DataStructrure.Tables.TempCreditCard.ActualTableName);
            //sql.Add(this.CurSQLFactory.SQL);

            // insert into tmp table
            for (int x = 0; x < ItemsCollection.Count; x++)
            {
                KeyParameters.Clear();
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.TransactionCode.ActualFieldName, ItemsCollection[x].TransactionCode));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.TransactionDate.ActualFieldName, ItemsCollection[x].TransactionDate));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.CreditCardNo.ActualFieldName, ItemsCollection[x].CardNumber));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.EmployeeID.ActualFieldName, ItemsCollection[x].EmployeeNumber));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.AmountSpent.ActualFieldName, ItemsCollection[x].SpentAmount.ToString()));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.CurrencySpent.ActualFieldName, ItemsCollection[x].SpentCurrency.InternalID));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.AmountFinal.ActualFieldName, ItemsCollection[x].FinalAmount.ToString()));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.CurrencyFinal.ActualFieldName, ItemsCollection[x].FinalCurrency.InternalID));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.ExhchangeRate.ActualFieldName, ItemsCollection[x].ExchangeRate.ToString()));
                KeyParameters.Add(new DatabaseParameter(this.DataStructrure.Tables.TempCreditCard.Description.ActualFieldName, ItemsCollection[x].Description.Replace("'","''"), true));

                this.CurSQLFactory.InsertCommand(KeyParameters, this.DataStructrure.Tables.TempCreditCard.ActualTableName);
                sql.Add(this.CurSQLFactory.SQL);
            }

            // transfer to actual table
            this.CurSQLFactory.ExecuteStoredProcedure(this.DataStructrure.StoredProcedures.TransferCreditCard.ActualTableName, (new DatabaseParameters()));
            sql.Add(this.CurSQLFactory.SQL);

            // run sql
            if ((smooth = this.TryConnection()))
            {
                if (!(smooth = this.CurDBEngine.ExecuteQuery(sql)))
                {
                    this.ErrMsg = "[CreditCardManager.InjectCreditCardItems] : this.CurDBEngine.ExecuteQuery(sql) : " + this.CurDBEngine.ErrorMessage;
                }
            }

            return smooth;
        }
        #endregion

        #region GetExceptionCreditCardItems
        public DataTable GetExceptionCreditCardItems()
        {
            DataTable ResultTable= new DataTable();
            bool smooth = true;
            ArrayList sql = new ArrayList();
            DatabaseParameters KeyParameters = new DatabaseParameters();

           // get exception credit card from tmp_creditcard
            this.CurSQLFactory.ExecuteStoredProcedure(this.DataStructrure.StoredProcedures.TransferExceptionCreditCard.ActualTableName, (new DatabaseParameters()));
            ResultTable = this.CurDBEngine.SelectQuery(this.CurSQLFactory.SQL);


            if ((smooth = this.TryConnection()))
            {
                if (!(ResultTable != null))
                {
                    this.ErrMsg = "[CreditCardManager.GetExceptionCreditCardItems] : this.CurDBEngine.SelectQuery(sql) : " + this.CurDBEngine.ErrorMessage;
                }
            }
            return ResultTable;
        }
        #endregion

        #endregion

        #region Protected

        #region QueryActualCreditCard
        protected DataTable QueryActualCreditCard(DatabaseParameters KeyParameters)
        {            
            return this.QueryData(KeyParameters, this.DataStructrure.Tables.CreditCardActual.ActualTableName);
        }
        #endregion

        #region QueryActualCreditCardView
        protected DataTable QueryActualCreditCardView(DatabaseParameters KeyParameters)
        {
            return this.QueryData(KeyParameters, this.DataStructrure.Views.OpCCActual.ActualTableName);
        }
        #endregion

        #region LoadCreditCardItemsInActual
        protected CreditCardItems LoadCreditCardItemsInActual(DataTable ResultTable)
        {
            CreditCardItems ResultItems = null;

            if (ResultTable != null)
            {
                ResultItems = new CreditCardItems();

                foreach (DataRow ResultRow in ResultTable.Rows)
                {
                    CreditCardItem CurrentItem = new CreditCardItem(ResultRow[this.DataStructrure.Tables.CreditCardActual.InternalID.ActualFieldName].ToString());
                    CurrentItem.CardNumber = ResultRow[this.DataStructrure.Tables.CreditCardActual.CardNo.ActualFieldName].ToString();
                    CurrentItem.Description = ResultRow[this.DataStructrure.Tables.CreditCardActual.Description.ActualFieldName].ToString();
                    CurrentItem.EmployeeNumber = ResultRow[this.DataStructrure.Tables.CreditCardActual.EmployeeNo.ActualFieldName].ToString();
                    CurrentItem.ExchangeRate = Convert.ToDecimal(ResultRow[this.DataStructrure.Tables.CreditCardActual.ExchangeRate.ActualFieldName].ToString());
                    CurrentItem.FinalAmount = Convert.ToDecimal(ResultRow[this.DataStructrure.Tables.CreditCardActual.AmountFinal.ActualFieldName].ToString());
                    CurrentItem.FinalCurrency = new Currency(
                        ResultRow[this.DataStructrure.Tables.CreditCardActual.CurrencyFinal.ActualFieldName].ToString(),
                        ResultRow[this.DataStructrure.Tables.CreditCardActual.CurrencyFinal.ActualFieldName].ToString());
                    CurrentItem.IsSelected = (ResultRow[this.DataStructrure.Tables.CreditCardActual.IsSelected.ActualFieldName].ToString().CompareTo("1") == 0);                    
                    CurrentItem.PaymentType = CreditCardManager.GetCreditCardPaymentTypeFromCode(Convert.ToInt32(ResultRow[this.DataStructrure.Tables.CreditCardActual.PaymentType.ActualFieldName].ToString()));
                    CurrentItem.SpentAmount = Convert.ToDecimal(ResultRow[this.DataStructrure.Tables.CreditCardActual.AmountSpent.ActualFieldName].ToString());
                    CurrentItem.SpentCurrency = new Currency(
                        ResultRow[this.DataStructrure.Tables.CreditCardActual.CurrencySpent.ActualFieldName].ToString(),
                        ResultRow[this.DataStructrure.Tables.CreditCardActual.CurrencySpent.ActualFieldName].ToString());
                    CurrentItem.TransactionCode = ResultRow[this.DataStructrure.Tables.CreditCardActual.TransactionCode.ActualFieldName].ToString();
                    CurrentItem.TransactionDate = DateTime.Parse(ResultRow[this.DataStructrure.Tables.CreditCardActual.TransactionDate.ActualFieldName].ToString());
                    CurrentItem.AttachedToClaimHeaderID = ResultRow[this.DataStructrure.Tables.CreditCardActual.ClaimHeaderID.ActualFieldName].ToString();
                    CurrentItem.AttachedToClaimDetailID = ResultRow[this.DataStructrure.Tables.CreditCardActual.ClaimItemID.ActualFieldName].ToString();
                    CurrentItem.IsDuplicated = (Convert.ToInt32(ResultRow[this.DataStructrure.Tables.CreditCardActual.IsDuplicated.ActualFieldName]) == 1);

                    ResultItems.Add(CurrentItem);
                }
            }

            return ResultItems;
        }
        #endregion        
        
        #endregion

    }
}
